package main;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;

public class connect {
	private static String fName = "Game.config";
	
	 public static String Dev_URL() {
		 String sitio = "http://www.winegame.org/admin/";
		 return sitio;
	 }
	 public static Connection connect_DB() {
         Connection conn = null;
       try {
    	   
    	   String url = "jdbc:mysql://winegame.ctqo8fsbjphq.us-east-1.rds.amazonaws.com:3306/";
    	  //String url = "jdbc:mysql://127.0.0.1:3306/";
           
            String dbName = "winegame";
            String userName =  "root";
            String password =  "agronomo";
            Properties props = new Properties();
            try {
                Class.forName("com.mysql.jdbc.Driver");
                //String connectionUrl = "jdbc:mysql://130.207.94.29:80/temperature;user=root;password=agronomo";
                //conn = DriverManager.getConnection(connectionUrl);
                conn = DriverManager.getConnection(url + dbName, userName, password);

            } catch (ClassNotFoundException e) {;
                System.exit(1);
            } catch (SQLException e) {
                JOptionPane.showMessageDialog( null,
             "Error in DB connection!",
             "Error", JOptionPane.ERROR_MESSAGE);
            }

        } catch (Exception ex) {
            Logger.getLogger(main_window.class.getName()).log(Level.SEVERE, null, ex);
        }
         return conn;
  }
	
	 public static int getStatus() {
		 int stat=0;
		 try {
			  String query = "SELECT Winery, Importer, Distributor, Retailer FROM Game_status WHERE ((Game_ID=?) AND (Week=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek());
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				 stat=rs.getInt(1)+rs.getInt(2)+rs.getInt(3)+rs.getInt(4);
			  }
			  ps.close();
			  conn.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return stat;
	 }
	 public static void pop_tablagames(JTable Tabla) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[5];
		    try {
		  		  String query = "SELECT ID, Game_Name, Game_Description, pass, sem FROM view_game_stat Order by ID ASC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  result[0]=rs.getInt(1); 
		  			 result[1]=rs.getString(2); 
		  			 result[2]=rs.getString(3); 
		  			 if (rs.getInt(4)==1) {
		  				result[3]="Yes"; 
		  			 } else {
		  				result[3]="No";
		  			 }
		  			if (rs.getInt(5)==0) {
		  				result[4]="Not Started"; 
		  			 } else {
		  				result[4]=rs.getString(5);
		  			 }
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.getColumnModel().getColumn(0).setMaxWidth(0);
				    Tabla.getColumnModel().getColumn(0).setMinWidth(0);
				    Tabla.getColumnModel().getColumn(0).setWidth(0);
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 }
	 public static boolean lookPass(String pas, int ID){
		 boolean check = false;
		 try {
			  String query = "SELECT passwd FROM games_id WHERE ((ID=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,ID);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				if (rs.getString(1).compareTo(pas)==0) check=true;
			  }
			  rs.close();
			  ps.close();
			  conn.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 
		 return check;
	 }
	 
	 public static void pop_tablaOrd(JTable Tabla) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		    Object[] result = new Object[6];
		    try {
		  		 
		  		  Connection conn =  connect_DB();
		  		  for (int i =values.getWeek();i>=-4;i--) {
		  	      
		  		  String query = "SELECT Semana, level, Order_white, Order_Red  FROM orders WHERE ((Game_ID=?) AND (Semana=?))";
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, i);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {  		
		  				result[0] = rs.getInt(1);
		  				switch (rs.getInt(2)) {
		  				case 4 : result[1] = rs.getInt(3) + " / " + rs.getInt(4);
		  				break;
		  				case 3 : result[2] = rs.getInt(3) + " / " + rs.getInt(4);
	  					break;
		  				case 2: result[3] = rs.getInt(3) + " / " + rs.getInt(4);
	  					break;
		  				case 1 : result[4] = rs.getInt(3) + " / " + rs.getInt(4);
	  					break;

		  				}
		  		  }
		  		  
				    rs.close();
				    ps.close();
			  		   query = "SELECT Bot_white, Bot_Red  FROM production WHERE ((Game_ID=?) AND (Semana=?))";
			  		   ps = conn.prepareStatement(query);
			  		   ps.setInt(1, values.getIDGame());
			  		   ps.setInt(2, i);
			  		   rs = ps.executeQuery();
			  		   while ( rs.next() ) {
			  			 result[5] = rs.getInt(1) + " / " + rs.getInt(2);
			  		   }
			  		 rs.close();
					  ps.close();
			  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
		  		  

				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 public static int [] Invback(int lev) throws SQLException {

		        int[] result = new int[4];
		    try {
		  		  String query = "SELECT Red_Inv,Red_back,White_Inv,White_Back FROM orders_inv_back WHERE ((Game_ID=?) AND (Level=?) AND (Semana=?))";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ps.setInt(3, values.getWeek());
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<4;i++){
		  				result[i] = rs.getInt(i+1);
		  			  }
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();

		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
		    return result;
	 } 
}
